/*-----------------------------------------------------------------------------------
Name: Sangyoon Park
Date: July 23 2020
This do file does : 
	Create variable labels and export codebook
-----------------------------------------------------------------------------------*/
clear all
set matsize 10000
set maxvar 10000
set more off


#delim ;

*** Load dataset;

import excel "${raw_path}/PESTICIDE_RESULT.xlsx", firstrow;

*** Label dataset;

lab data "This file contains farmer pesticide test results";

*** Label variables;

label var pest1_result "Acetamiprid";
label var pest2_result "Azoxystrobin";
label var pest3_result "Carbendazim";
label var pest4_result "Chlorothalonil";
label var pest5_result "Chlorpyrifos";
label var pest6_result "Cyprodinil";
label var pest7_result "Difenoconazole";
label var pest8_result "Fipronil";
label var pest9_result "Hexaconazole";
label var pest10_result "Metalaxyl";
label var pest11_result "Permethrin";
label var pest12_result "Prochloraz";
label var pest13_result "Pyraclostrobin";
label var pest14_result "Tebuconazole";
label var pest15_result "Thiabendazole";
label var pest16_result "Dithiocarbamates";
label var pest17_result "Phenthoate";
label var pest18_result "Quinalphos";

*** Pesticide result over MRL ***;

** EU MRL DATABASE;

local pest1_mrl_eu = 0.01;
local pest2_mrl_eu = 0.3;
local pest3_mrl_eu = 0.1;
local pest4_mrl_eu = 0.01;
local pest5_mrl_eu = 0.01;
local pest6_mrl_eu = 0.02;
local pest7_mrl_eu = 0.15;
local pest8_mrl_eu = 0.005;
local pest9_mrl_eu = 0.01;
local pest10_mrl_eu = 0.01;
local pest11_mrl_eu = 0.05;
local pest12_mrl_eu = 0.05;
local pest13_mrl_eu = 0.02;
local pest14_mrl_eu = 0.02;
local pest15_mrl_eu = 0.01;
local pest16_mrl_eu = 0.05;
local pest17_mrl_eu = 0;
local pest18_mrl_eu = 0.01;

** US MRL DATABASE;

local pest1_mrl_us = 0.5;
local pest2_mrl_us = 2;
local pest3_mrl_us = 0.01;
local pest4_mrl_us = 0.5;
local pest5_mrl_us = 0.1;
local pest6_mrl_us = 2;
local pest7_mrl_us = 1.5;
local pest8_mrl_us = 0.01;
local pest9_mrl_us = 0.01;
local pest10_mrl_us = 4;
local pest11_mrl_us = 1;
local pest12_mrl_us = 0.01;
local pest13_mrl_us = 0.04;
local pest14_mrl_us = 0.05;
local pest15_mrl_us = 3;
local pest16_mrl_us = 0.01;
local pest17_mrl_us = 0.01;
local pest18_mrl_us = 0.01;

** China/HK MRL DATABASE;

local pest1_mrl_china = 2;
local pest2_mrl_china = 0.3;
local pest3_mrl_china = 0.5;
local pest4_mrl_china = 0.2;
local pest5_mrl_china = 0.3;
local pest6_mrl_china = 0.5;
local pest7_mrl_china = 0.05;
local pest8_mrl_china = 0.02;
local pest9_mrl_china = 0.05;
local pest10_mrl_china = 0.2;
local pest11_mrl_china = 2;
local pest12_mrl_china = 7;
local pest13_mrl_china = 0.05;
local pest14_mrl_china = 0.05;
local pest15_mrl_china = 3;
local pest16_mrl_china = 2;
local pest17_mrl_china = 1;
local pest18_mrl_china = 0.5;

** Japan MRL DATABASE;

local pest1_mrl_japan = 0.2;
local pest2_mrl_japan = 1;
local pest3_mrl_japan = 0.2;
local pest4_mrl_japan = 0.2;
local pest5_mrl_japan = 0.05;
local pest6_mrl_japan = 0.3;
local pest7_mrl_japan = 0.07;
local pest8_mrl_japan = 0.005;
local pest9_mrl_japan = 0.2;
local pest10_mrl_japan = 0.2;
local pest11_mrl_japan = 2;
local pest12_mrl_japan = 0.05;
local pest13_mrl_japan = 0.02;
local pest14_mrl_japan = 0.1;
local pest15_mrl_japan = 3;
local pest16_mrl_japan = 0.6;
local pest17_mrl_japan = 0.1;
local pest18_mrl_japan = 0.02;

*** Generate variables for analysis;

local pestcountry "eu us china japan";

foreach y of local pestcountry{;

gen sum_overmrl_`y' = 0;
gen sum_undermrl_`y' = 0;

	forvalues i = 1(1)18 {;
	
		gen overmrl_pest`i'_`y' = (pest`i'_result > `pest`i'_mrl_`y'');
		replace sum_overmrl_`y' = sum_overmrl_`y' + overmrl_pest`i'_`y';
		gen undermrl_pest`i'_`y' = (pest`i'_result <= `pest`i'_mrl_`y'');
		replace sum_undermrl_`y' = sum_undermrl_`y' + undermrl_pest`i'_`y';
		};

gen anypest_overmrl_`y' = (sum_overmrl_`y' > 0);
gen frac_undermrl_`y' = sum_undermrl_`y'/18;

	forvalues i = 1(1)18 {;
	
		gen std_pest`i'_`y' = pest`i'_result/`pest`i'_mrl_`y'';
		};

egen mean_std_pest_`y' = rmean(std_pest1_`y' std_pest3_`y' std_pest7_`y' std_pest9_`y' std_pest10_`y' std_pest11_`y' std_pest16_`y');

forvalues i = 1(1)18{;

	gen cond_std_pest`i'_`y' = std_pest`i'_`y' if overmrl_pest`i'_`y' == 1;
	
	};
	
egen cond_mean_pest_`y' = rmean(cond_std_pest1_`y' cond_std_pest2_`y' cond_std_pest3_`y' cond_std_pest4_`y' cond_std_pest5_`y'
						cond_std_pest6_`y' cond_std_pest7_`y' cond_std_pest8_`y' cond_std_pest9_`y' cond_std_pest10_`y' cond_std_pest11_`y'
						cond_std_pest12_`y' cond_std_pest13_`y' cond_std_pest14_`y' cond_std_pest15_`y' cond_std_pest16_`y' cond_std_pest17_`y'
						cond_std_pest18_`y');		

gen overmrl_haz2_`y' = (overmrl_pest5_`y' == 1 | overmrl_pest7_`y' == 1 | overmrl_pest8_`y' == 1 | overmrl_pest10_`y' == 1 | overmrl_pest11_`y' == 1 | overmrl_pest12_`y' == 1 | overmrl_pest14_`y' == 1 | overmrl_pest17_`y' == 1 | overmrl_pest18_`y' == 1);
gen overmrl_haz3_`y' = (overmrl_pest9_`y' == 1 | overmrl_pest15_`y' == 1);
gen overmrl_haz2or3_`y' = (overmrl_pest9_`y' == 1 | overmrl_pest15_`y' == 1 | overmrl_pest5_`y' == 1 | overmrl_pest7_`y' == 1 | overmrl_pest8_`y' == 1 | overmrl_pest10_`y' == 1 | overmrl_pest11_`y' == 1 | overmrl_pest12_`y' == 1 | overmrl_pest14_`y' == 1 | overmrl_pest17_`y' == 1 | overmrl_pest18_`y' == 1);
gen overmrl_hazu_`y' = (overmrl_pest2_`y' == 1 | overmrl_pest3_`y' == 1 | overmrl_pest4_`y' == 1);
gen overmrl_hazuk_`y' = (overmrl_pest1_`y' == 1 | overmrl_pest6_`y' == 1 | overmrl_pest13_`y' == 1 | overmrl_pest16_`y' == 1);

*** Label generated variables;

label var overmrl_pest1_`y' "Acetamiprid";
label var overmrl_pest2_`y' "Azoxystrobin";
label var overmrl_pest3_`y' "Carbendazim";
label var overmrl_pest4_`y' "Chlorothalonil";
label var overmrl_pest5_`y' "Chlorpyrifos";
label var overmrl_pest6_`y' "Cyprodinil";
label var overmrl_pest7_`y' "Difenoconazole";
label var overmrl_pest8_`y' "Fipronil";
label var overmrl_pest9_`y' "Hexaconazole";
label var overmrl_pest10_`y' "Metalaxyl";
label var overmrl_pest11_`y' "Permethrin";
label var overmrl_pest12_`y' "Prochloraz";
label var overmrl_pest13_`y' "Pyraclostrobin";
label var overmrl_pest14_`y' "Tebuconazole";
label var overmrl_pest15_`y' "Thiabendazole";
label var overmrl_pest16_`y' "Dithiocarbamates";
label var overmrl_pest17_`y' "Phenthoate";
label var overmrl_pest18_`y' "Quinalphos";						
	
};	
	

keep if center == 2;

save "${data_path}/PEST_HV_MRL", replace;

clear;









